﻿using ATM.Common;
using ATM.Entities;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ATM.DAO
{
    public class AccountDAO
    {
        private static SqlConnection con;
        public static Account GetCurrentAccount(Card card)
        {
            try
            {
                Account acc = new Account();
                con = DataConnection.GetConnection();
                string query = "Select AccountID,CustID,Balance,AccountNo,Value1=WithDrawLimits.Value,Value2=OverDraftLimits.Value From Accounts,WithDrawLimits,OverDraftLimits WHERE Accounts.WDID=WithDrawLimits.WDID AND Accounts.ODID=OverDraftLimits.ODID";
                SqlCommand cmd = new SqlCommand(query, con);
                SqlDataReader reader = null;
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    if (reader["AccountID"].ToString() == card.AccID)
                    {
                        acc.ID = reader["AccountID"].ToString();
                        acc.CusID = reader["CustID"].ToString();
                        acc.Balance = float.Parse(reader["Balance"].ToString());
                        acc.AccountNo = reader["AccountNo"].ToString();
                        acc.WithdrawLimit = Int32.Parse(reader["Value1"].ToString());
                        acc.OverDraftLimit = Int32.Parse(reader["Value2"].ToString());
                        break;
                    }
                }
                return acc;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static bool UpdateAccount(Account acc)
        {
            try
            {
                con = DataConnection.GetConnection();
                SqlCommand cmd = new SqlCommand("UPDATE Accounts SET Balance=@Balance WHERE AccountID=@AccID", con);
                cmd.Parameters.Add(new SqlParameter("@Balance", acc.Balance));
                cmd.Parameters.Add(new SqlParameter("@AccID", acc.ID));
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static bool UpdateAccountMoney(string cardId, float money)
        {
            try
            {
                con = DataConnection.GetConnection();
                SqlCommand cmd0 = new SqlCommand(String.Format("SELECT AccID FROM Cards WHERE ID LIKES '{0}'", cardId), con);
                SqlDataReader reader = cmd0.ExecuteReader();
                while (reader.Read())
                {
                    var cusId = (string)reader[0];
                    reader.Close();
                    SqlCommand cmd1 = new SqlCommand(String.Format("select Balance, OverDraft from Accounts where ID = '{0}'", cusId), con);
                    SqlDataAdapter da = new SqlDataAdapter(cmd1);
                    DataTable dt = new DataTable();
                    da.Fill(dt);

                    double _balance = Convert.ToDouble(dt.Rows[0][0].ToString());
                    double _overdraft = Convert.ToDouble(dt.Rows[0][1].ToString());
                    if (money > _balance)
                    {
                        _overdraft = _overdraft + (money - _balance);
                        _balance = money;
                    }
                    else _balance = _balance - money;

                    string query = "UPDATE Accounts SET Balance = " + _balance
                            + ", OverDraft = " + _overdraft + " WHERE ID = " + cusId;
                    SqlCommand cmdUpdateAccount = new SqlCommand(query, con);
                    int success = cmdUpdateAccount.ExecuteNonQuery();
                    if (success > 0) return true;
                    return false;
                }

            }
            catch (Exception)
            {
                throw;
            }

            finally
            {
                con.Close();
            }

            return false;
        }

    }
}
